# -*- coding:utf-8 -*-
import os
import pymysql

# select * from cat where node like '%,142,%' and id<>142 and level<(select level from cat where id=142)+2
# select * from cat where parent=143

# 无极分类, 如果level不为1, node为:  根据父级的node+自身id+','
arr = {'全部商品分类': {'计算机设备': ['台式计算机', '便携式计算机', '液晶显示器'], '网络设备': ['服务器', '交换机'], '打印设备': ['A4激光打印机', 'A3激光打印机', '喷墨打印机', '针式打印机'], '办公设备': ['复印机', '复印机配件', '速印机', '多功能一体机', '碎纸机', '投影仪', '投影仪配件', '扫描仪'], '电器设备': ['电视机', '空调机', '电冰箱'], '摄像机': ['摄像机套机', '摄像机配件'], '照相机': ['照相机套机', '照相机配件'], '办公用纸': ['复印纸'], '办公自动化设备耗材': ['硒鼓', '墨盒/墨水', '复印机粉盒/墨粉', '色带'], '视频会议系统设备': ['专业音箱', '功放', '话筒', 'DVD播放器', '数字音频处理器', 'HDMI分配器', 'HDMI切换器']}}
mysql_config = {
    'host': '127.0.0.1',  # ip地址(本机地址可以写 localhost 或 127.0.0.1)
    'port': 3306,  # 端口号
    'user': 'root',  # 用户名
    'database': 'test',  # 数据库名
    'charset': 'utf8',  # 字符集
}
connect = pymysql.connect(**mysql_config)
cursor = connect.cursor()  # 创建游标

# 一级分类
# 无极分类,level为1,node为','+自身id+','
first_node = ',1,'
first_parent = '0'
first_name = '全部商品分类'
first_level = '1'
source = '4'


try:
    # 插入语句
    sql = 'insert into cat (name,source,parent,level) values ('+'"'+first_name+'"'+','+source+','+first_parent+','+first_level+')'
    # 执行插入语句
    result = cursor.execute(sql)
except Exception as e:
    print(e)
else:
    connect.commit()


# 获取最后一条插入数据的id
first_id = cursor.lastrowid
node = ','+str(first_id)+','
try:
    # 更新语句
    sql = 'update cat set node='+'"'+node+'"'+' where id='+str(first_id)
    # 执行插入语句
    result = cursor.execute(sql)
except Exception as e:
    print(e)
else:
    connect.commit()


for k, v in arr['全部商品分类'].items():
    # 二级分类
    name = k
    parent = str(first_id)
    level = '2'
    # 先插入数据自动生成自增长id
    try:
        # 插入语句
        sql = 'insert into cat (name,source,parent,level) values (' + '"' + name + '"' + ',' + source + ',' + parent + ',' + level + ')'
        # 执行插入语句
        result = cursor.execute(sql)
    except Exception as e:
        print(e)
        print(2)

    else:
        connect.commit()
    second_id = cursor.lastrowid
    second_node = node+str(second_id)+','
    try:
        # 更新语句
        sql = 'update cat set node=' + '"' + second_node + '"' + ' where id=' + str(second_id)
        # 执行插入语句
        result = cursor.execute(sql)
    except Exception as e:
        print(e)
        print(2)
    else:
        connect.commit()
    for i in v:
        min_name = i
        min_parent = str(second_id)
        min_level = '3'
        # 执行插入
        try:
            # 插入语句
            sql = 'insert into cat (name,source,parent,level) values (' + '"' + min_name + '"' + ',' + source + ',' + min_parent + ',' + min_level + ')'
            # 执行插入语句
            result = cursor.execute(sql)
        except Exception as e:
            print(e)
            print(3)
        else:
            connect.commit()
            # min_id = 获取最近插入数据id

        min_id = cursor.lastrowid
        min_id = str(min_id)
        min_node = second_node + min_id + ','
        try:
            # 更新语句
            sql = 'update cat set node=' + '"' + min_node + '"' + ' where id=' + min_id
            # 执行插入语句
            result = cursor.execute(sql)
        except Exception as e:
            print(e)
            print(3)
        else:
            connect.commit()

cursor.close()
connect.close()